import re
import time
import paramiko
from global_parameter import get_su_path, sql_loading, restart_cvt_client


def ssh_exe_cmd(ssh, cmd):
    stdin, stdout, stderr = ssh.exec_command(cmd)
    output_str = stdout.read().decode('utf-8')
    return output_str


def channel_exe_cmd(channel, cmd, end_flag="# "):
    channel.send(cmd)  # 执行linux指令
    channel.send("\n")  # 回车
    time.sleep(0.1)
    buff = ''
    while not buff.endswith(end_flag):
        resp = channel.recv(9999)
        # 出现异常字符时忽略
        buff += resp.decode('utf-8', errors='ignore')
        # print(buff)
    return buff


def channel_exe_cmd_test(channel, cmd, end_flag="# "):
    channel.send(cmd)
    channel.send("\n")
    time.sleep(0.1)
    buff = ''
    start_time = time.time()
    while end_flag not in buff:
        assert time.time() - start_time < 260, "cmd命令超时260秒:{}".format(cmd)
        resp = channel.recv(9999)
        # 出现异常字符时忽略
        buff += resp.decode('utf-8', errors='ignore')
        # print(buff)
        time.sleep(1)
    return buff


def initial_data(ip, port, username, password):
    # 初始化概率性失败，添加五次重试机制
    for i in range(5):
        try:
            # 建立一个sshclient对象
            ssh = paramiko.SSHClient()
            # 允许将信任的主机自动加入到host_allow 列表，此方法必须放在connect方法的前面
            ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
            ssh.connect(hostname=ip, port=port, username=username, password=password)
            channel = ssh.invoke_shell()
            # 设置接收数据的超时时间（以秒为单位）
            channel.settimeout(60)  # 这里将超时时间设置为60秒

            # 获取root权限 end_flag:输出结束的标志，当指令执行结束，linux窗口显示 "操作系统密码："
            output_str = channel_exe_cmd_test(channel, 'sudo {} --debug'.format(get_su_path), end_flag='操作系统密码：')
            # print(output_str)
            # python 正则表达式 re模块
            match_data = re.findall(r"操作系统密码：\d{6}", output_str)
            # print(match_data)
            su_password = match_data[-1][-6:]
            # print(su_password)
            channel_exe_cmd_test(channel, "su", end_flag='密码：')
            time.sleep(0.5)
            channel_exe_cmd_test(channel, su_password)

            start_time = time.time()
            # 清除数据库并恢复数据库
            channel_exe_cmd_test(channel, 'mysql -uoetsky -pOetsky@123', end_flag='> ')
            channel_exe_cmd_test(channel, 'drop database cvt_client_prod;', end_flag='> ')
            channel_exe_cmd_test(channel, 'create database cvt_client_prod;', end_flag='> ')
            channel_exe_cmd_test(channel, r"use cvt_client_prod;", end_flag='> ')
            # 禁用索引和外键检查
            channel_exe_cmd_test(channel, r"SET FOREIGN_KEY_CHECKS=0;", end_flag='> ')
            channel_exe_cmd_test(channel, r"SET UNIQUE_CHECKS=0;", end_flag='> ')
            channel_exe_cmd_test(channel, r"SET AUTOCOMMIT=0;", end_flag='> ')
            print("开始导入")
            channel_exe_cmd(channel, sql_loading, end_flag='> ')
            # 恢复索引和外键检查
            channel_exe_cmd_test(channel, r"SET FOREIGN_KEY_CHECKS=1;", end_flag='> ')
            channel_exe_cmd_test(channel, r"SET UNIQUE_CHECKS=1;", end_flag='> ')
            channel_exe_cmd_test(channel, r"SET AUTOCOMMIT=1;", end_flag='> ')
            print("结束导入")
            print("导入数据库操作耗时：" + str(time.time() - start_time))
            channel_exe_cmd_test(channel, r"exit", end_flag='[root@localhost oetsky]# ')

            # 使用root权限重启站端服务
            print("开始重启站端服务")
            channel_exe_cmd_test(channel, "\r", end_flag="[root@localhost oetsky]# ")
            time.sleep(1)
            channel_exe_cmd_test(channel, restart_cvt_client, "---> cvt_client_run.sh:433")
            print("重启站端服务结束")
            print("导入数据库操作+重启站端共耗时：" + str(time.time() - start_time))
            # 2023-9-23发现测试过程中发现重启站端服务之后，打开网页tcp连接不上的情况，添加延时5秒看情况是否能改善
            time.sleep(5)
            break
        except:
            print("初始化超时，重试次数：{}".format(i))


if __name__ == '__main__':
    initial_data("192.168.31.13", port=10088, username="oetsky", password="oetsky@87942")
